import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'package:flutter/foundation.dart';

class DatabaseHelper {
  static final DatabaseHelper _instance = DatabaseHelper._internal();
  static DatabaseHelper get instance => _instance;
  DatabaseHelper._internal();

  Database? _database;

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  Future<Database> _initDatabase() async {
    final String path = join(await getDatabasesPath(), 'moods.db');
    
    return await openDatabase(
      path,
      version: 2,
      onCreate: (Database db, int version) async {
        debugPrint('Creating new database...');
        await db.execute('''
          CREATE TABLE moods(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            date TEXT NOT NULL,
            moodType INTEGER NOT NULL,
            intensity INTEGER NOT NULL,
            triggers TEXT,
            bodyReactions TEXT,
            description TEXT,
            imagePath TEXT,
            createdAt TEXT NOT NULL
          )
        ''');
      },
      onUpgrade: (Database db, int oldVersion, int newVersion) async {
        debugPrint('Upgrading database from version $oldVersion to $newVersion');
        if (oldVersion < 2) {
          // 检查列是否已存在
          var table = await db.rawQuery('PRAGMA table_info(moods)');
          bool hasImagePath = table.any((column) => column['name'] == 'imagePath');
          
          if (!hasImagePath) {
            await db.execute('ALTER TABLE moods ADD COLUMN imagePath TEXT');
            debugPrint('Added imagePath column to moods table');
          }
        }
      },
      onDowngrade: onDatabaseDowngradeDelete,  // 如果版本降级，删除数据库重新创建
    );
  }

  // 仅在需要时才使用这个方法重置数据库
  Future<void> resetDatabase() async {
    debugPrint('Resetting database...');
    final String path = join(await getDatabasesPath(), 'moods.db');
    await deleteDatabase(path);
    _database = null;
    await database;
    debugPrint('Database reset completed');
  }

  // 获取指定日期的情绪记录
  Future<Map<String, dynamic>?> getMoodByDate(String date) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'moods',
      where: 'date = ?',
      whereArgs: [date],
      orderBy: 'createdAt DESC',
      limit: 1,
    );
    
    if (maps.isEmpty) {
      return null;
    }
    return maps.first;
  }

  // 获取日期范围内的情绪记录
  Future<List<Map<String, dynamic>>> getMoodsByDateRange(String startDate, String endDate) async {
    final db = await database;
    return await db.query(
      'moods',
      where: 'date BETWEEN ? AND ?',
      whereArgs: [startDate, endDate],
      orderBy: 'createdAt DESC',
    );
  }

  // 获取情绪统计数据
  Future<Map<int, int>> getMoodStats(String startDate, String endDate) async {
    final db = await database;
    final List<Map<String, dynamic>> result = await db.query(
      'moods',
      columns: ['moodType', 'COUNT(*) as count'],
      where: 'date BETWEEN ? AND ?',
      whereArgs: [startDate, endDate],
      groupBy: 'moodType',
    );

    final Map<int, int> stats = {};
    for (var row in result) {
      stats[row['moodType'] as int] = row['count'] as int;
    }
    return stats;
  }
} 